package com.fiveup.core.fuScore.mapper;

import com.fiveup.core.fuScore.model.ClassFuScore;
import com.fiveup.core.fuScore.model.ClassScore;
import com.fiveup.core.fuScore.model.StudentFuScore;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

@Mapper
@Repository
public interface ClassFuScoreMapper {

    @Select("select class_id, " +
            "FLOOR(AVG(morality_score)) as morality_score, " +
            "FLOOR(AVG(intelligence_score)) as intelligence_score, " +
            "FLOOR(AVG(physical_score)) as physical_score, " +
            "FLOOR(AVG(aesthetic_score)) as aesthetic_score, " +
            "FLOOR(AVG(labour_score)) as labour_score, " +
            "evaluate_date " +
            "from fu_student_score join basic_student on student_num=student_id where grade_id=#{gradeId} and class_id=#{classId} GROUP BY evaluate_date")
    List<ClassScore> getClassScore(int gradeId, int classId);

    @Select("select student_name, student_num, grade_id, class_id, " +
            "morality_score, intelligence_score, physical_score, aesthetic_score, labour_score, evaluate_date " +
            "from fu_student_score join basic_student on student_num=student_id " +
            "where grade_id=#{gradeId} and class_id=#{classId} and evaluate_date=#{date}")
    List<StudentFuScore> getStudentsFuScoreList(int gradeId, int classId, int date);

    @Select("SELECT morality_score, intelligence_score, physical_score, aesthetic_score, labour_score " +
            "FROM fu_student_score where student_num=#{studentNum} and evaluate_date=#{date}")
    StudentFuScore getLastScore(String studentNum, int date);

    @Select("select student_name, student_num, morality_score as score from fu_student_score where class_ID=#{classId}")
    List<ClassFuScore> getStudentsMoScoreList(Long classId);

    @Select("select student_name, student_num, intelligence_score as score from fu_student_score where class_ID=#{classId}")
    List<ClassFuScore> getStudentsInScoreList(Long classId);

    @Select("select student_name, student_num, physical_score as score from fu_student_score where class_ID=#{classId}")
    List<ClassFuScore> getStudentsPhScoreList(Long classId);

    @Select("select student_name, student_num, aesthetic_score as score from fu_student_score where class_ID=#{classId}")
    List<ClassFuScore> getStudentsAeScoreList(Long classId);

    @Select("select student_name, student_num, labour_score as score from fu_student_score where class_ID=#{classId}")
    List<ClassFuScore> getStudentsLaScoreList(Long classId);

    @Select("SELECT DISTINCT data FROM fu_grade_score")
    List<Integer> getClassDataList();

}
